/*
 * Copyright (C) 2000 - 2024 Silverpeas
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * As a special exception to the terms and conditions of version 3.0 of
 * the GPL, you may redistribute this Program in connection with Free/Libre
 * Open Source Software ("FLOSS") applications as described in Silverpeas's
 * FLOSS exception. You should have received a copy of the text describing
 * the FLOSS exception, and it is also available here:
 * "https://www.silverpeas.org/legal/floss_exception.html"
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 */
package org.silverpeas.core.persistence.jdbc.sql;

import com.ninja_squad.dbsetup.Operations;
import com.ninja_squad.dbsetup.operation.Insert;
import com.ninja_squad.dbsetup.operation.Operation;
import org.apache.commons.lang3.tuple.Pair;
import org.jboss.arquillian.container.test.api.Deployment;
import org.jboss.arquillian.junit.Arquillian;
import org.jboss.shrinkwrap.api.Archive;
import org.junit.Rule;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.silverpeas.core.persistence.Transaction;
import org.silverpeas.core.test.WarBuilder4LibCore;
import org.silverpeas.core.test.integration.rule.DbSetupRule;

import javax.annotation.Resource;
import javax.enterprise.concurrent.ManagedThreadFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.logging.Logger;

import static java.util.concurrent.TimeUnit.MILLISECONDS;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.*;
import static org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQuery.*;
import static org.silverpeas.core.test.util.TestRuntime.awaitUntil;

/**
 * Integration tests on the different features exposed by the {@link JdbcSqlQuery} technical class.
 */
@RunWith(Arquillian.class)
public class JdbcSqlQueryIT {

  public static final Operation TABLES_CREATION = Operations
      .sql("CREATE TABLE a_table (id int8 PRIMARY KEY NOT NULL , value varchar(50) NOT NULL)");
  public static final Operation TABLE_SET_UP;

  private final static long NB_ROW_AT_BEGINNING = 100L;

  static {
    Insert.Builder insertBulider = Operations.insertInto("a_table").columns("id", "value");
    for (long l = 0; l < NB_ROW_AT_BEGINNING; l++) {
      insertBulider.values(l, "value_" + l);
    }
    TABLE_SET_UP = insertBulider.build();
  }

  @Resource
  private ManagedThreadFactory managedThreadFactory;

  @Rule
  public DbSetupRule dbSetupRule = DbSetupRule.createTablesFrom("create_table_favorit_space.sql")
      .loadInitialDataSetFrom(TABLES_CREATION, TABLE_SET_UP);

  @Deployment
  public static Archive<?> createTestArchive() {
    return WarBuilder4LibCore.onWarForTestClass(JdbcSqlQueryIT.class)
        .addCommonBasicUtilities()
        .addSilverpeasExceptionBases()
        .build();
  }

  @Test
  public void deletionCommitWithExceptionAfterTransaction() throws Exception {
    List<String> result = executeMultiThreadedOperations(false);
    assertThat(String.join(System.lineSeparator(), result), is(String.join(System.lineSeparator(),
        "DELETION PROCESS - In transaction",
        "DELETION PROCESS - Waiting for verification",
        "VERIFICATION PROCESS - BEFORE DELETION - " + NB_ROW_AT_BEGINNING + " line(s)",
        "DELETION PROCESS - Waiting for verification (lines deleted)",
        "VERIFICATION PROCESS - AFTER DELETION - " + NB_ROW_AT_BEGINNING + " line(s)",
        "DELETION PROCESS - Transaction closed",
        "VERIFICATION PROCESS - AFTER TRANSACTION - 0 line(s)")));
  }


  @Test
  public void deletionRollbackWithExceptionDuringTransaction() throws Exception {
    List<String> result = executeMultiThreadedOperations(true);
    assertThat(String.join(System.lineSeparator(), result), is(String.join(System.lineSeparator(),
        "DELETION PROCESS - In transaction",
        "DELETION PROCESS - Waiting for verification",
        "VERIFICATION PROCESS - BEFORE DELETION - " + NB_ROW_AT_BEGINNING + " line(s)",
        "DELETION PROCESS - Waiting for verification (lines deleted)",
        "VERIFICATION PROCESS - AFTER DELETION - " + NB_ROW_AT_BEGINNING + " line(s)",
        "DELETION PROCESS - Perform rollback", "DELETION PROCESS - Rollback performed",
        "DELETION PROCESS - Transaction closed",
        "VERIFICATION PROCESS - AFTER TRANSACTION - " + NB_ROW_AT_BEGINNING + " line(s)")));
  }

  @SuppressWarnings("UnusedDeclaration")
  private List<String> executeMultiThreadedOperations(final boolean rollbackInTransaction)
      throws Exception {
    final Object monitorOfDeletion = new Object();
    final Object monitorOfVerification = new Object();
    assertThat(getTableLines(), hasSize((int) NB_ROW_AT_BEGINNING));
    final List<String> result = new ArrayList<>();
    Thread deletionProcess = managedThreadFactory.newThread(() -> {
      try {
        Transaction.performInOne(() -> {
          try (final Connection connection = dbSetupRule.getSafeConnectionFromDifferentThread()) {
            Transaction.performInOne(() -> {
              try (Connection otherConnection = dbSetupRule.getSafeConnectionFromDifferentThread()) {
                // Just opening two connections for pleasure :-)
                awaitUntil(50, MILLISECONDS);
                synchronized (monitorOfVerification) {
                  info(result, "DELETION PROCESS - In transaction");
                  info(result, "DELETION PROCESS - Waiting for verification");
                  monitorOfVerification.notifyAll();
                }
                synchronized (monitorOfDeletion) {
                  monitorOfDeletion.wait(500);
                }
                awaitUntil(10, MILLISECONDS);
                synchronized (monitorOfVerification) {
                  assertThat(deleteFrom("a_table").execute(), is(NB_ROW_AT_BEGINNING));
                  info(result, "DELETION PROCESS - Waiting for verification (lines deleted)");
                  monitorOfVerification.notifyAll();
                }
                synchronized (monitorOfDeletion) {
                  monitorOfDeletion.wait(500);
                }
                awaitUntil(10, MILLISECONDS);
              }
              return null;
            });
          }
          assertThat(getTableLines(), hasSize(0));
          if (rollbackInTransaction) {
            info(result, "DELETION PROCESS - Perform rollback");
            throw new IllegalArgumentException();
          }
          return null;
        });
      } catch (Exception e) {
        info(result, "DELETION PROCESS - Rollback performed");
      }
      synchronized (monitorOfVerification) {
        info(result, "DELETION PROCESS - Transaction closed");
        monitorOfVerification.notifyAll();
      }
    });
    Thread deletionVerifications = managedThreadFactory.newThread(() -> {
      try {
        synchronized (monitorOfVerification) {
          monitorOfVerification.wait(500);
        }
        awaitUntil(10, MILLISECONDS);
        synchronized (monitorOfDeletion) {
          info(result,
              "VERIFICATION PROCESS - BEFORE DELETION - " + getTableLines().size() + " line(s)");
          monitorOfDeletion.notifyAll();
        }
        synchronized (monitorOfVerification) {
          monitorOfVerification.wait(500);
        }
        awaitUntil(10, MILLISECONDS);
        synchronized (monitorOfDeletion) {
          info(result,
              "VERIFICATION PROCESS - AFTER DELETION - " + getTableLines().size() + " line(s)");
          monitorOfDeletion.notifyAll();
        }
        synchronized (monitorOfVerification) {
          monitorOfVerification.wait(500);
        }
        awaitUntil(10, MILLISECONDS);
        synchronized (monitorOfDeletion) {
          info(result,
              "VERIFICATION PROCESS - AFTER TRANSACTION - " + getTableLines().size() + " line(s)");
        }
      } catch (InterruptedException e) {
        throw new RuntimeException(e);
      }
    });

    List<Thread> threads = Arrays.asList(deletionVerifications, deletionProcess);
    for (Thread thread : threads) {
      thread.start();
    }
    for (Thread thread : threads) {
      thread.join(2000);
    }
    return result;
  }

  private void info(List<String> logContainer, String message) {
    logContainer.add(message);
    Logger.getAnonymousLogger().info("[" + Thread.currentThread().getId() + "] " + message);
  }

  @Test
  public void selectCountVerifications() throws SQLException {
    assertThat(countAll().from("a_table").execute(), is(NB_ROW_AT_BEGINNING));
    assertThat(countAll().from("a_table").where("id != ?", 8).execute(),
        is(NB_ROW_AT_BEGINNING - 1));
    assertThat(countAll().from("a_table").where("id != ?", 8).and("LENGTH(value) <= ?", 7).execute(),
        is(9L));
  }

  @Test
  public void selectAll() throws SQLException {
    List<Pair<Long, String>> rows =
        select("* from a_table").execute(new TableResultProcess());
    long l = 0;
    assertThat(rows, hasSize((int) NB_ROW_AT_BEGINNING));
    for (Pair<Long, String> row : rows) {
      assertThat(row.getLeft(), is(l));
      l++;
    }

    int resultLimit = (int) (NB_ROW_AT_BEGINNING - 10);
    rows = select("* from a_table").configure(config -> config.withResultLimit(resultLimit))
        .execute(new TableResultProcess());
    assertThat(rows, hasSize(resultLimit));
  }

  @Test(expected = IllegalArgumentException.class)
  public void selectAllButUsingUnique() throws SQLException {
    select("* from a_table").executeUnique(new TableResultProcess());
  }

  @Test
  public void selectOneParameter() throws SQLException {
    List<Pair<Long, String>> rows = select("*").from("a_table").where("id = ?", 30)
        .execute(new TableResultProcess());
    assertThat(rows, hasSize(1));
    var uniqueRow = unique(rows);
    assertThat(uniqueRow, is(notNullValue()));
    assertThat(uniqueRow.getLeft(), is(30L));
    rows = select("*").from("a_table").where("id = ?", 200).execute(new TableResultProcess());
    assertThat(rows, hasSize(0));
    assertThat(unique(rows), nullValue());
  }

  @Test
  public void selectUsingOneAppendParameter() throws SQLException {
    List<Pair<Long, String>> rows =
        select("*").from("a_table").where("id = ?", 26).execute(new TableResultProcess());
    assertThat(rows, hasSize(1));
    var uniqueRow = unique(rows);
    assertThat(uniqueRow, is(notNullValue()));
    assertThat(uniqueRow.getLeft(), is(26L));
  }

  @Test
  public void selectUsingTwoAppendParametersAndAppendListOfParameters() throws SQLException {
    JdbcSqlQuery sqlQuery = select("*").from("a_table").where("id = ?", 26)
      .or("LENGTH(value) <= ?", 7)
      .or("id").in(38, 39, 40);
    List<Pair<Long, String>> rows = sqlQuery.execute(new TableResultProcess());
    assertThat(rows, hasSize(14));
  }

  @Test
  public void selectUsingAppendListOfParameters() throws SQLException {
    List<Pair<Long, String>> rows =
        select("*").from("a_table").where("id").in(38, 39, 40).execute(new TableResultProcess());
    assertThat(rows, hasSize(3));
  }

  private static class TableResultProcess implements SelectResultRowProcess<Pair<Long, String>> {
    private int rowIndex = 0;
    private boolean assertion;

    TableResultProcess() {
      this(true);
    }

    TableResultProcess(boolean assertion) {
      this.assertion = assertion;
    }

    @Override
    public Pair<Long, String> currentRow(final ResultSetWrapper row) throws SQLException {
      try {
        if (assertion) {
          assertThat(row.getCurrentRowIndex(), is(rowIndex));
        }
        return Pair.of(row.getLongObject(1), row.getString(2));
      } finally {
        rowIndex = rowIndex + 1;
      }
    }
  }

  @Test
  public void selectWithOffsetAndLimitAll() throws SQLException {
    List<Pair<Long, String>> rows = select("*").from("a_table").where("value like ?", "%0")
        .orderBy("id desc").execute(new TableResultProcess());
    assertThat(rows, hasSize(10));
    assertThat(rows.get(0).getRight(), is("value_90"));
    assertThat(rows.get(1).getRight(), is("value_80"));

    int resultLimit = 5;

    rows = select("*").from("a_table").where("value like ?", "%0").orderBy("id desc")
        .configure(config -> config.withResultLimit(resultLimit)).execute(new TableResultProcess());

    assertThat(rows, hasSize(resultLimit));
    assertThat(rows.get(0).getRight(), is("value_90"));
    assertThat(rows.get(1).getRight(), is("value_80"));

    int offset = 2;

    rows = select("*").from("a_table").where("value like ?", "%0").orderBy("id desc")
        .configure(config -> config.withOffset(offset))
        .configure(config -> config.withResultLimit(resultLimit)).execute(new TableResultProcess(false));

    assertThat(rows, hasSize(resultLimit));
    assertThat(rows.get(0).getRight(), is("value_70"));
    assertThat(rows.get(1).getRight(), is("value_60"));
  }

  @Test
  public void createRowUsingAppendSaveParameter() {
    assertThat(getTableLines(), hasSize(100));
    JdbcSqlQuery insertSqlQuery = JdbcSqlQuery.insertInto("a_table");
    insertSqlQuery.withInsertParam("id", 200);
    insertSqlQuery.withInsertParam("value", "value_200_inserted");
    Transaction.performInOne(() -> {
      long insertCount = insertSqlQuery.execute();
      assertThat(insertCount, is(1L));
      return null;
    });
    assertThat(getTableLines(), hasSize(101));
    assertThat(getTableLines().get(100), is("200@value_200_inserted"));
  }

  @Test
  public void updateTwoRowsFromThreeUpdatesUsingAppendSaveParameter() {
    assertThat(getTableLines().get(0), is("0@value_0"));
    assertThat(getTableLines().get(26), is("26@value_26"));
    assertThat(getTableLines().get(38), is("38@value_38"));

    JdbcSqlQuery firstInsertSqlQuery = update("a_table");
    firstInsertSqlQuery.withUpdateParam("value", "value_26_updated");
    firstInsertSqlQuery.where("id = ?", 26);

    JdbcSqlQuery secondInsertSqlQuery = update("a_table");
    secondInsertSqlQuery.withUpdateParam("value", "value_38_updated");
    secondInsertSqlQuery.where("id = ?", 38);

    JdbcSqlQuery thirdInsertSqlQuery = update("a_table");
    thirdInsertSqlQuery.withUpdateParam("value", "value_200_updated");
    thirdInsertSqlQuery.where("id = ?", 200);

    Transaction.performInOne(() -> {
      long updateCount = JdbcSqlExecutorProvider.getJdbcSqlExecutor()
          .executeModify(firstInsertSqlQuery, secondInsertSqlQuery, thirdInsertSqlQuery);
      assertThat(updateCount, is(2L));
      return null;
    });

    assertThat(getTableLines().get(0), is("0@value_0"));
    assertThat(getTableLines().get(26), is("26@value_26_updated"));
    assertThat(getTableLines().get(38), is("38@value_38_updated"));
  }

  @Test
  public void updateTwoRowsFromThreeUpdatesUsingAppendSaveParameterWithExistingConnection() {
    assertThat(getTableLines().get(0), is("0@value_0"));
    assertThat(getTableLines().get(26), is("26@value_26"));
    assertThat(getTableLines().get(38), is("38@value_38"));

    JdbcSqlQuery firstInsertSqlQuery = update("a_table");
    firstInsertSqlQuery.withUpdateParam("value", "value_26_updated");
    firstInsertSqlQuery.where("id = ?", 26);

    JdbcSqlQuery secondInsertSqlQuery = update("a_table");
    secondInsertSqlQuery.withUpdateParam("value", "value_38_updated");
    secondInsertSqlQuery.where("id = ?", 38);

    JdbcSqlQuery thirdInsertSqlQuery = update("a_table");
    thirdInsertSqlQuery.withUpdateParam("value", "value_200_updated");
    thirdInsertSqlQuery.where("id = ?", 200);

    Transaction.performInOne(() -> {
      try (Connection connection = dbSetupRule.getSafeConnectionFromDifferentThread()) {
        long updateCount = JdbcSqlExecutorProvider.getJdbcSqlExecutor()
            .executeModify(connection, firstInsertSqlQuery, secondInsertSqlQuery,
                thirdInsertSqlQuery);
        assertThat(updateCount, is(2L));
        return null;
      }
    });

    assertThat(getTableLines().get(0), is("0@value_0"));
    assertThat(getTableLines().get(26), is("26@value_26_updated"));
    assertThat(getTableLines().get(38), is("38@value_38_updated"));
  }

  @Test
  public void deleteRows() {
    assertThat(getTableLines(), hasSize(100));
    Transaction.performInOne(() -> {
      long deleteCount = deleteFrom("a_table").where("LENGTH(value) <= ?", 7).execute();
      assertThat(deleteCount, is(10L));
      return null;
    });
    assertThat(getTableLines(), hasSize(90));
  }

  @Test
  public void dropTableA() throws SQLException {
    assertThat(countAll().from("INFORMATION_SCHEMA.TABLES").where("lower(TABLE_NAME) = ?", "a_table")
        .execute(), is(1L));
    Transaction.performInOne(() -> {
      dropTable("a_table").execute();
      return null;
    });
    assertThat(countAll().from("INFORMATION_SCHEMA.TABLES").where("lower(TABLE_NAME) = ?", "a_table")
        .execute(), is(0L));
  }

  @Test
  public void createTableB() throws SQLException {
    assertThat(countAll().from("INFORMATION_SCHEMA.TABLES").where("lower(TABLE_NAME) = ?", "b_table")
        .execute(), is(0L));
    Transaction.performInOne(() -> {
      createTable("b_table").addField("identifier", "integer primary key")
          .addField("description", "varchar(50) NOT NULL").execute();
      insertInto("b_table").withInsertParam("identifier", 26)
          .withInsertParam("description", "Drôme").execute();
      return null;
    });
    assertThat(countAll().from("INFORMATION_SCHEMA.TABLES").where("lower(TABLE_NAME) = ?", "b_table")
        .execute(), is(1L));
    assertThat(countAll().from("b_table").execute(), is(1L));
  }

  /**
   * Gets the content of a_table.
   * @return the content of a_table.
   */
  private List<String> getTableLines() {
    try (Connection connection = dbSetupRule.getSafeConnectionFromDifferentThread();
         PreparedStatement statement = connection
             .prepareStatement("SELECT * FROM a_table ORDER BY id");
         ResultSet resultSet = statement.executeQuery()) {
      List<String> result = new ArrayList<>();
      while (resultSet.next()) {
        result.add(resultSet.getLong(1) + "@" + resultSet.getString(2));
      }
      return result;
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
}